--    Program: 		CAPARIMP_CUSTOMER_LOCATION.sql
--    Description:  Import AR CUSTOMER LOCATION information 
--    Revision:     1st     
--    Parameter: 	
--    Created By:   Kenny Leung
--    Date:         1-Feb-2006

set doc off;      
set verify off;
set serveroutput on size 1000000;
--whenever sqlerror exit failure rollback;

declare   
   l_processed_count		number;
   l_rejected_count 		number;
   l_err_msg				varchar2(2000);
   l_err_msg_tmp 			varchar2(2000);
   
   l_location_id			number;
                    		                 		
   cursor cur_ar_customer_location is
      select * from CRST_HZ_LOCATIONS
      for update;

begin
   l_processed_count := 0;
   l_rejected_count  := 0;
   l_err_msg := '';
   
   for cur in cur_ar_customer_location loop   	
     l_err_msg_tmp := '';
     
     select max(nvl(location_id,0)) + 1
     INTO l_location_id 
     FROM HZ_LOCATIONS;
     
     update CRST_HZ_LOCATIONS
     set    location_id_n = l_location_id
     where  location_id = cur.LOCATION_ID;
     
     begin
       -- import data
       insert into HZ_LOCATIONS
       (
              LOCATION_ID                   ,
              LAST_UPDATE_DATE              ,
              LAST_UPDATED_BY               ,
              CREATION_DATE                 ,
              CREATED_BY                    ,
              LAST_UPDATE_LOGIN             ,
              REQUEST_ID                    ,
              PROGRAM_APPLICATION_ID        ,
              PROGRAM_ID                    ,
              PROGRAM_UPDATE_DATE           ,
              WH_UPDATE_DATE                ,
              ATTRIBUTE_CATEGORY            ,
              ATTRIBUTE1                    ,
              ATTRIBUTE2                    ,
              ATTRIBUTE3                    ,
              ATTRIBUTE4                    ,
              ATTRIBUTE5                    ,
              ATTRIBUTE6                    ,
              ATTRIBUTE7                    ,
              ATTRIBUTE8                    ,
              ATTRIBUTE9                    ,
              ATTRIBUTE10                   ,
              ATTRIBUTE11                   ,
              ATTRIBUTE12                   ,
              ATTRIBUTE13                   ,
              ATTRIBUTE14                   ,
              ATTRIBUTE15                   ,
              ATTRIBUTE16                   ,
              ATTRIBUTE17                   ,
              ATTRIBUTE18                   ,
              ATTRIBUTE19                   ,
              ATTRIBUTE20                   ,
              GLOBAL_ATTRIBUTE_CATEGORY     ,
              GLOBAL_ATTRIBUTE1             ,
              GLOBAL_ATTRIBUTE2             ,
              GLOBAL_ATTRIBUTE3             ,
              GLOBAL_ATTRIBUTE4             ,
              GLOBAL_ATTRIBUTE5             ,
              GLOBAL_ATTRIBUTE6             ,
              GLOBAL_ATTRIBUTE7             ,
              GLOBAL_ATTRIBUTE8             ,
              GLOBAL_ATTRIBUTE9             ,
              GLOBAL_ATTRIBUTE10            ,
              GLOBAL_ATTRIBUTE11            ,
              GLOBAL_ATTRIBUTE12            ,
              GLOBAL_ATTRIBUTE13            ,
              GLOBAL_ATTRIBUTE14            ,
              GLOBAL_ATTRIBUTE15            ,
              GLOBAL_ATTRIBUTE16            ,
              GLOBAL_ATTRIBUTE17            ,
              GLOBAL_ATTRIBUTE18            ,
              GLOBAL_ATTRIBUTE19            ,
              GLOBAL_ATTRIBUTE20            ,
              ORIG_SYSTEM_REFERENCE         ,
              COUNTRY                       ,
              ADDRESS1                      ,
              ADDRESS2                      ,
              ADDRESS3                      ,
              ADDRESS4                      ,
              CITY                          ,
              POSTAL_CODE                   ,
              STATE                         ,
              PROVINCE                      ,
              COUNTY                        ,
              ADDRESS_KEY                   ,
              ADDRESS_STYLE                 ,
              VALIDATED_FLAG                ,
              ADDRESS_LINES_PHONETIC        ,
              APARTMENT_FLAG                ,
              PO_BOX_NUMBER                 ,
              HOUSE_NUMBER                  ,
              STREET_SUFFIX                 ,
              APARTMENT_NUMBER              ,
              SECONDARY_SUFFIX_ELEMENT      ,
              STREET                        ,
              RURAL_ROUTE_TYPE              ,
              RURAL_ROUTE_NUMBER            ,
              STREET_NUMBER                 ,
              BUILDING                      ,
              FLOOR                         ,
              SUITE                         ,
              ROOM                          ,
              POSTAL_PLUS4_CODE             ,
              TIME_ZONE                     ,
              OVERSEAS_ADDRESS_FLAG         ,
              POST_OFFICE                   ,
              POSITION                      ,
              DELIVERY_POINT_CODE           ,
              LOCATION_DIRECTIONS           ,
              ADDRESS_EFFECTIVE_DATE        ,
              ADDRESS_EXPIRATION_DATE       ,
              ADDRESS_ERROR_CODE            ,
              CLLI_CODE                     ,
              DODAAC                        ,
              TRAILING_DIRECTORY_CODE       ,
              LANGUAGE                      ,
              LIFE_CYCLE_STATUS             ,
              SHORT_DESCRIPTION             ,
              DESCRIPTION                   ,
              CONTENT_SOURCE_TYPE           ,
              LOC_HIERARCHY_ID              ,
              SALES_TAX_GEOCODE             ,
              SALES_TAX_INSIDE_CITY_LIMITS  ,
              FA_LOCATION_ID                ,
              --GEOMETRY                      ,
              OBJECT_VERSION_NUMBER         ,
              CREATED_BY_MODULE             ,
              APPLICATION_ID                ,
              TIMEZONE_ID                   ,
              GEOMETRY_STATUS_CODE          ,
              ACTUAL_CONTENT_SOURCE          
       )
       values
       (
            l_location_id                     ,
            cur.LAST_UPDATE_DATE              ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.LAST_UPDATED_BY),
            cur.CREATION_DATE                 ,
            CAPBRIMP_MAPPING.GetUserMapping(cur.CREATED_BY),
            cur.LAST_UPDATE_LOGIN             ,
            cur.REQUEST_ID                    ,
            cur.PROGRAM_APPLICATION_ID        ,
            cur.PROGRAM_ID                    ,
            cur.PROGRAM_UPDATE_DATE           ,
            cur.WH_UPDATE_DATE                ,
            cur.ATTRIBUTE_CATEGORY            ,
            cur.ATTRIBUTE1                    ,
            cur.ATTRIBUTE2                    ,
            cur.ATTRIBUTE3                    ,
            cur.ATTRIBUTE4                    ,
            cur.ATTRIBUTE5                    ,
            cur.ATTRIBUTE6                    ,
            cur.ATTRIBUTE7                    ,
            cur.ATTRIBUTE8                    ,
            cur.ATTRIBUTE9                    ,
            cur.ATTRIBUTE10                   ,
            cur.ATTRIBUTE11                   ,
            cur.ATTRIBUTE12                   ,
            cur.ATTRIBUTE13                   ,
            cur.ATTRIBUTE14                   ,
            cur.ATTRIBUTE15                   ,
            cur.ATTRIBUTE16                   ,
            cur.ATTRIBUTE17                   ,
            cur.ATTRIBUTE18                   ,
            cur.ATTRIBUTE19                   ,
            cur.ATTRIBUTE20                   ,
            cur.GLOBAL_ATTRIBUTE_CATEGORY     ,
            cur.GLOBAL_ATTRIBUTE1             ,
            cur.GLOBAL_ATTRIBUTE2             ,
            cur.GLOBAL_ATTRIBUTE3             ,
            cur.GLOBAL_ATTRIBUTE4             ,
            cur.GLOBAL_ATTRIBUTE5             ,
            cur.GLOBAL_ATTRIBUTE6             ,
            cur.GLOBAL_ATTRIBUTE7             ,
            cur.GLOBAL_ATTRIBUTE8             ,
            cur.GLOBAL_ATTRIBUTE9             ,
            cur.GLOBAL_ATTRIBUTE10            ,
            cur.GLOBAL_ATTRIBUTE11            ,
            cur.GLOBAL_ATTRIBUTE12            ,
            cur.GLOBAL_ATTRIBUTE13            ,
            cur.GLOBAL_ATTRIBUTE14            ,
            cur.GLOBAL_ATTRIBUTE15            ,
            cur.GLOBAL_ATTRIBUTE16            ,
            cur.GLOBAL_ATTRIBUTE17            ,
            cur.GLOBAL_ATTRIBUTE18            ,
            cur.GLOBAL_ATTRIBUTE19            ,
            cur.GLOBAL_ATTRIBUTE20            ,
            cur.ORIG_SYSTEM_REFERENCE         ,
            cur.COUNTRY                       ,
            cur.ADDRESS1                      ,
            cur.ADDRESS2                      ,
            cur.ADDRESS3                      ,
            cur.ADDRESS4                      ,
            cur.CITY                          ,
            cur.POSTAL_CODE                   ,
            cur.STATE                         ,
            cur.PROVINCE                      ,
            cur.COUNTY                        ,
            cur.ADDRESS_KEY                   ,
            cur.ADDRESS_STYLE                 ,
            cur.VALIDATED_FLAG                ,
            cur.ADDRESS_LINES_PHONETIC        ,
            cur.APARTMENT_FLAG                ,
            cur.PO_BOX_NUMBER                 ,
            cur.HOUSE_NUMBER                  ,
            cur.STREET_SUFFIX                 ,
            cur.APARTMENT_NUMBER              ,
            cur.SECONDARY_SUFFIX_ELEMENT      ,
            cur.STREET                        ,
            cur.RURAL_ROUTE_TYPE              ,
            cur.RURAL_ROUTE_NUMBER            ,
            cur.STREET_NUMBER                 ,
            cur.BUILDING                      ,
            cur.FLOOR                         ,
            cur.SUITE                         ,
            cur.ROOM                          ,
            cur.POSTAL_PLUS4_CODE             ,
            cur.TIME_ZONE                     ,
            cur.OVERSEAS_ADDRESS_FLAG         ,
            cur.POST_OFFICE                   ,
            cur.POSITIONS                     ,
            cur.DELIVERY_POINT_CODE           ,
            cur.LOCATION_DIRECTIONS           ,
            cur.ADDRESS_EFFECTIVE_DATE        ,
            cur.ADDRESS_EXPIRATION_DATE       ,
            cur.ADDRESS_ERROR_CODE            ,
            cur.CLLI_CODE                     ,
            cur.DODAAC                        ,
            cur.TRAILING_DIRECTORY_CODE       ,
            cur.LANGUAGE                      ,
            cur.LIFE_CYCLE_STATUS             ,
            cur.SHORT_DESCRIPTION             ,
            cur.DESCRIPTION                   ,
            cur.CONTENT_SOURCE_TYPE           ,
            cur.LOC_HIERARCHY_ID              ,
            cur.SALES_TAX_GEOCODE             ,
            cur.SALES_TAX_INSIDE_CITY_LIMITS  ,
            cur.FA_LOCATION_ID                ,
            --cur.GEOMETRY                      ,
            cur.OBJECT_VERSION_NUMBER         ,
            cur.CREATED_BY_MODULE             ,
            cur.APPLICATION_ID                ,
            cur.TIMEZONE_ID                   ,
            cur.GEOMETRY_STATUS_CODE          ,
            cur.ACTUAL_CONTENT_SOURCE                    
       );  
     exception
       when others then
     	 l_err_msg_tmp := sqlerrm;
     end;	  	    
     
     if l_err_msg_tmp = '' then
       l_processed_count := l_processed_count + 1;  
     else
       l_rejected_count := l_rejected_count + 1;
       l_err_msg := l_err_msg_tmp; 
     end if;  	  
   end loop;
   
   if l_rejected_count <> 0 then
   	 commit;
   else
     rollback;
   end if;
   
   -- insert into log table
   insert into capcnv_log ( prog_id,process_date, record_processed,record_rejected,record_extracted,err_msg) 
    values ('CAPARIMP_CUSTOMER_LOCATION', sysdate, l_processed_count, l_rejected_count, l_processed_count-l_rejected_count, l_err_msg );
   
   commit;
exception 
  when others then
    dbms_output.put_line(sqlerrm);
    rollback;  
end;
/


--exit
